﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using webapp4U;
using System.IO;

public partial class UserControl_ControlNhapSoLieuTuNPPDKSH : System.Web.UI.UserControl
{
    string strconn = webapp4U.Configuration.CurrentConnectionString;
    string error = "";
    ClassExecute.ClassExecute dt;
    string strSiteCodeDistributor = "FM_00000003"; ////DKSH
    protected void Page_Load(object sender, EventArgs e)
    {
        Pager.IndexChanged += new EventHandler(PageIndex_Changed);
        if (!IsPostBack)
        {
            MakeTableTempImport();
        }
        lblFileName.Text = txtFileName.Value;
        VisibleLinkbtnMsg();

        //lblMsg.Text = "";
        //lblMsg.Visible = false;
    }

    void MakeTableTempImport()
    {
        dt = new ClassExecute.ClassExecute();
        dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "MakeTempImportDKSH", ref error, new SqlParameter("@UserName", Session["user"].ToString()));

    }
    protected void drlNhomSanPham_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            string foundID = String.Empty;

            
            // get the value from the first dropdownlist
            GridViewRow row = (GridViewRow)((sender) as Control).NamingContainer;
            DropDownList drlNhomSanPham = ((sender) as DropDownList);
            DropDownList drlKenhSanPham = row.FindControl("drlKenhSanPham") as DropDownList;

            dt = new ClassExecute.ClassExecute();
            DataTable db1 = dt.Get_Data_DataTable(strconn, ref error, "[k_getInclassFilter]", CommandType.StoredProcedure,
               new SqlParameter("@CatID", drlNhomSanPham.SelectedValue.ToString()));
            dt.BindDropDownList(db1, drlKenhSanPham, "MaNhanHieu", "NhanHieu");


            //string firstDropDownListID = drlNhomSanPham.UniqueID;

            // now from here you can do whatever you want! 
            //LoadNhanHieu(drlNhomSanPham.SelectedValue.ToString());

        }
        catch
        {

        }
    }

    private void PageIndex_Changed(Object sender, EventArgs e)
    {
        DataBind();
    }

    public override void DataBind()
    {
        // hien thi số dòng tren 1 trang
        Pager.PageSize = 10;

        dt = new ClassExecute.ClassExecute();
        DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempImportDKSH_GetAll", CommandType.StoredProcedure,
             new SqlParameter("@UserName", Session["user"].ToString()), new SqlParameter("@PageIndex", Pager.PageIndex), new SqlParameter("@PageSize", Pager.PageSize));
        if (db.Rows.Count > 0)
        {
            gviewList.DataSource = db;
            gviewList.DataBind();
            lblMsg.Text = "";
            lblMsg.Visible = true;

            Pager.TotalRecords = 0;
            db = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempImportDKSH_CountGetAll", CommandType.StoredProcedure, new SqlParameter("@UserName", Session["user"].ToString()));
            if (db.Rows.Count > 0)
            {
                try
                {
                    Pager.TotalRecords = int.Parse(db.Rows[0][0].ToString());
                }
                catch { }
            }

            // kiem tra xem co can hien thi phan trang ko
            if (Pager.TotalRecords <= 1)
                DisplayPager.Visible = false;
            else
                DisplayPager.Visible = true;
        }
        else
        {
            lblMsg.Text = "Chưa có số liệu.";
            lblMsg.Visible = true;
        }       
        
    }

    void ShowMessageBox(string text)
    {
        lblMessageBox.Text = text;
        lblMsgResultNewCust.Text = "";
        lblMsgResultNewCust.Visible = false;
        MessageBoxNewCustomer.ShowOnPageLoad = true;

    }
    void ShowMessageBoxNewProduct(string text)
    {
        lblMessageBoxNewProduct.Text = text;
        lblMsgResultNewProduct.Text = "";
        lblMsgResultNewProduct.Visible = false;
        MessageBoxNewProduct.ShowOnPageLoad = true;

    }
    protected void grvInValidList_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grvInValidList.PageIndex = e.NewPageIndex;
        dt = new ClassExecute.ClassExecute();
        DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempImportDKSH_CheckInValid",
            CommandType.StoredProcedure, new SqlParameter("@UserName", Session["user"].ToString()));
        if (db.Rows.Count > 0)
        {
            grvInValidList.DataSource = db;
            grvInValidList.DataBind();
        }
    }
    protected void grvListExistOrder_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grvListExistOrder.PageIndex = e.NewPageIndex;
        dt = new ClassExecute.ClassExecute();
        DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempImportDKSH_CheckExistOrderData", 
            CommandType.StoredProcedure, new SqlParameter("@UserName", Session["user"].ToString()));
        if (db.Rows.Count > 0)
        {
            grvListExistOrder.DataSource = db;
            grvListExistOrder.DataBind();
        }
    }
    protected void btnOkExistOrder_Click(object sender, EventArgs e)
    {
        MessageBoxExistOrder.ShowOnPageLoad = false;
        if (txtFileName.Value != "")
        {
            string YyyyMm = DateTime.Now.ToString("yyyyMM");
            string pathFolder = @"UserUpload\ImportedFileExcel\" + YyyyMm;
            InsertDataImport(pathFolder, txtfileNameDestinationInsert.Value);
        }
        else
        {
            lblMsg.Text = "Chưa tải tập tin lên hệ thống. Bạn phải chọn tập tin Excel cần import.";
            lblMsg.Visible = true;
        }
    }
    protected void btnCancelExistOrder_Click(object sender, EventArgs e)
    {
        MessageBoxExistOrder.ShowOnPageLoad = false;        
        //dua file ve vi trí ban dau.
        string YyyyMm = DateTime.Now.ToString("yyyyMM");
        string pathFolder = @"UserUpload\ImportedFileExcel\" + YyyyMm;
        string physicalPathFolder = @"/UserUpload/ImportedFileExcel/" + YyyyMm + "/";
        string filePathSource = webapp4U.Globals.PhysicalApplicationPath() + physicalPathFolder + txtfileNameDestinationInsert.Value;
        try
        {
            File.Move(filePathSource, webapp4U.Globals.PhysicalApplicationPath() + @"/UserUpload/UploadTempFileExcel/" + txtFileName.Value);
        }
        catch (Exception exc)
        {
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), exc.Message, method);
        }
        return;
        lblExistOrder.Text = "";
        lblExistOrder.Visible = false;
        grvListExistOrder.DataSource = null;
        grvListExistOrder.DataBind();
        
    }
    private void InsertDataImport(string pathFolder, string fileNameDestination)
    {
        dt = new ClassExecute.ClassExecute();
        if (dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "TTr_UploadFile_Insert", ref error,
            new SqlParameter("@SiteCode", strSiteCodeDistributor),
            new SqlParameter("@FileNameOrginaly", txtFileName.Value),
            new SqlParameter("@FileNameDestination", fileNameDestination),
            new SqlParameter("@PathFolder", pathFolder),
            new SqlParameter("@UserUpload", Session["user"].ToString()),
            new SqlParameter("@Title", txtTitle.Text)))
        {

            dt = new ClassExecute.ClassExecute();
            
            //bool boolresult = false;
            //boolresult = dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "TTr_InsertDataImportDKSHDemo", ref error, new SqlParameter("@UserName",Session["user"].ToString()));
            
            int intresult = 0;
            intresult = dt.ExecuteSQl_int(strconn, "TTr_InsertDataImportDKSHDemo", CommandType.StoredProcedure, ref error, new SqlParameter("@UserName", Session["user"].ToString()));


            if (intresult==1)
            {
                dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "D_History_Insert", ref error,
                    new SqlParameter("@UserName", Session["user"].ToString()), new SqlParameter("@OrdinaryFileName", txtFileName.Value),
                    new SqlParameter("@DestinationFileName", fileNameDestination), new SqlParameter("@Action", Action.Import),
                    new SqlParameter("@Time", DateTime.Now));
                txtFileName.Value = "";
                lblFileName.Text = "";
                txtfileNameDestination.Value = "";
                txtfileNameDestinationInsert.Value = "";

                lblMsg.Text = "Đã cập nhật số liệu thành công.";
                lblMsg.Visible = true;

                gviewList.DataSource = null;
                gviewList.DataBind();
                DisplayPager.Visible = false;
                VisibleLinkbtnMsg();
                DataTable dtNewCust = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempNewCustDKSH_GetAll", CommandType.StoredProcedure);
                if (dtNewCust.Rows.Count > 0)
                {
                    gvrNewCustList.DataSource = dtNewCust;
                    gvrNewCustList.DataBind();
                    btnActiveCustNewAll.Visible = true;
                    ShowMessageBox("Danh sách khách hàng mới đã được thêm vào hệ thống. Bạn hãy xác nhận những khách hàng này để được tính doanh số.");
                }

                DataTable dbNewProduct = dt.Get_Data_DataTable(strconn, ref error, "TempProductDKSH_GetAll", CommandType.StoredProcedure);
                if (dbNewProduct.Rows.Count > 0)
                {
                    gvrNewProductList.DataSource = dbNewProduct;
                    gvrNewProductList.DataBind();
                    //LoadNganhHang();
                    //LoadNhanHieu(drlNhomSanPham.SelectedValue.ToString());
                    ShowMessageBoxNewProduct("Danh sách sản phẩm mới đã được thêm vào hệ thống. Bạn hãy xác nhận, cập nhật thông tin cho những sản phẩm này.");
                }

            }
            else if (intresult == 2)
            { 
                lblFileName.Text = "";
                txtFileName.Value = "";
                txtfileNameDestination.Value = "";
                txtfileNameDestinationInsert.Value = "";

                lblMsg.Text = "Dữ liệu không được import vào hệ thống.Dữ liệu đã cũ không phải của tháng hiện tại.";
                lblMsg.Visible = true;
                dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "TTr_UploadFile_Remove", ref error, new SqlParameter("@FileNameDestination", fileNameDestination));
            }
            else
            {
                lblFileName.Text = "";
                txtFileName.Value = "";
                txtfileNameDestination.Value = "";
                txtfileNameDestinationInsert.Value = "";

                lblMsg.Text = "Cập nhật số liệu thất bại";
                lblMsg.Visible = true;
                dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "TTr_UploadFile_Remove", ref error, new SqlParameter("@FileNameDestination", fileNameDestination));
            }
        }    

    }
    private void ImportFileExcel()
    {
        dt = new ClassExecute.ClassExecute();
        string YyyyMm = DateTime.Now.ToString("yyyyMM");
        string pathFolder = @"UserUpload\ImportedFileExcel\" + YyyyMm;
        string savepath = "";
        savepath = webapp4U.Globals.PhysicalApplicationPath() + pathFolder;
        string fileNameDestination = webapp4U.Common.GetFileName() + "_" + txtFileName.Value;
        txtfileNameDestination.Value = fileNameDestination;
        DataTable dtFileNameCopy = dt.Get_Data_DataTable(strconn, ref error, "TTr_UploadFile_CreateFileNameCopyDateNow", CommandType.StoredProcedure,
            new SqlParameter("@FileNameOrginaly", txtFileName.Value),
            new SqlParameter("@SiteCode", strSiteCodeDistributor));
        if (dtFileNameCopy.Rows.Count > 0)
        {
            fileNameDestination = fileNameDestination.Insert(fileNameDestination.LastIndexOf('.'), dtFileNameCopy.Rows[0][0].ToString());
        }
        txtfileNameDestinationInsert.Value = fileNameDestination;

        if (!Directory.Exists(savepath))
            Directory.CreateDirectory(savepath);        
        string physicalPathFolder = @"/UserUpload/ImportedFileExcel/" + YyyyMm + "/";

        //Rename and Move fiel to  ImportedFileExcel/        
        string filePathSource = webapp4U.Globals.PhysicalApplicationPath() + @"/UserUpload/UploadTempFileExcel/" + txtFileName.Value;
        string filePathDes = webapp4U.Globals.PhysicalApplicationPath() + physicalPathFolder + fileNameDestination;
        try
        {
            File.Move(filePathSource, filePathDes);
        }
        catch (Exception exc)
        {
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), exc.Message, method);
        }

        ////Import data to table Temp
        try
        {
            if (Excel2Sql(physicalPathFolder, fileNameDestination))
            {
                //Kiểm tra format dữ liệu.
                DataTable dtListInvalid = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempImportDKSH_CheckInValid",
                    CommandType.StoredProcedure, new SqlParameter("@UserName", Session["user"].ToString()));
                if (dtListInvalid.Rows.Count > 0)
                {
                    lblInValidData.Text = "Danh sách những dòng dữ liệu không hợp lệ. File này hiện tại chưa được cập nhật vào hệ thống. Đề nghị bạn kiểm tra lại file.";
                    lblInValidData.Visible = true;
                    grvInValidList.DataSource = dtListInvalid;
                    grvInValidList.DataBind();
                    MessageBoxInValidData.ShowOnPageLoad = true;
                    //dua file ve vi trí ban dau.
                    try
                    {
                        File.Move(filePathDes, filePathSource);
                    }
                    catch (Exception exc)
                    {
                        string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
                        dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), exc.Message, method);
                    }
                    return;
                }
                else
                {
                    lblInValidData.Text = "";
                    lblInValidData.Visible = false;
                    grvInValidList.DataSource = null;
                    grvInValidList.DataBind();
                    MessageBoxInValidData.ShowOnPageLoad = false;
                }

                dtListInvalid = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempImportDKSH_CheckExistOrderData",
                    CommandType.StoredProcedure, new SqlParameter("@UserName", Session["user"].ToString()));
                if (dtListInvalid.Rows.Count > 0)
                {
                    lblExistOrder.Text = "Danh sách những hóa đơn bị trùng với hệ thống.";
                    lblExistOrder.Visible = true;
                    grvListExistOrder.DataSource = dtListInvalid;
                    grvListExistOrder.DataBind();
                    MessageBoxExistOrder.ShowOnPageLoad = true;
                    return;
                }
                else
                {
                    lblExistOrder.Text = "";
                    lblExistOrder.Visible = false;
                    grvListExistOrder.DataSource = null;
                    grvListExistOrder.DataBind();
                    MessageBoxExistOrder.ShowOnPageLoad = false;
                }
                // End kiểm tra format dữ liệu
                InsertDataImport(pathFolder, fileNameDestination);

            }
            else
            {
                try
                {
                    File.Move(filePathDes, filePathSource);
                }
                catch (Exception exc)
                {
                    string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
                    dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), exc.Message, method);
                }
                return;
            }
        }
        catch (Exception exc)
        {
            txtFileName.Value = "";
            lblFileName.Text = "";
            lblMsg.Text = "Tập tin " + txtFileName.Value + " không đúng định dạng của NPP";
            lblMsg.Visible = true;
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), exc.Message, method);
        }

    }
    protected void btnOk_Click(object sender, EventArgs e)
    {
        MessageBoxFileDuplicate.ShowOnPageLoad = false;        
        if (txtFileName.Value != "")
        {
            ImportFileExcel();
        }
        else
        {
            lblMsg.Text = "Chưa tải tập tin lên hệ thống. Bạn phải chọn tập tin Excel cần import.";
            lblMsg.Visible = true;
        }        
        
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {       

        lblMsgDuplicateFileName.Text = "";
        lblMsgDuplicateFileName.Visible = false;
        lblMsgDuplicateFile.Text = "";
        lblMsgDuplicateFile.Visible = false;
        MessageBoxFileDuplicate.ShowOnPageLoad = false;
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (txtFileName.Value != "")
        {
            bool checkduplicate = false;
            //check file Import có trùng
            string filePathSource = webapp4U.Globals.PhysicalApplicationPath() + @"/UserUpload/UploadTempFileExcel/" + txtFileName.Value;

            dt = new ClassExecute.ClassExecute();
            DataTable dtFileNameCopy = dt.Get_Data_DataTable(strconn, ref error, "TTr_UploadFile_CreateFileNameCopyDateNow", CommandType.StoredProcedure,
                new SqlParameter("@FileNameOrginaly", txtFileName.Value),
                new SqlParameter("@SiteCode", strSiteCodeDistributor));
            if (dtFileNameCopy.Rows.Count > 0)
            {
                lblMsgDuplicateFileName.Text = "Tên file Import bị trùng.";
                lblMsgDuplicateFileName.Visible = true;
                checkduplicate = true;
            }
            else
            {
                lblMsgDuplicateFileName.Text = "";
                lblMsgDuplicateFileName.Visible = false;
            }
            DataTable ListFile = dt.Get_Data_DataTable(strconn, ref error, "TTr_UploadFile_GetListFileCheckDuplicate", CommandType.StoredProcedure
                , new SqlParameter("@Sitecode", strSiteCodeDistributor));

            if (ListFile.Rows.Count > 0)
            {
                CheckFile cf = new CheckFile();
                for (int i = 0; i < ListFile.Rows.Count; i++)
                {

                    if (cf.FileCompare(filePathSource, webapp4U.Globals.PhysicalApplicationPath() +
                        ListFile.Rows[i]["PathFolder"].ToString() + @"\" + ListFile.Rows[i]["FileNameDestination"].ToString()))
                    {
                        lblMsgDuplicateFile.Text = "Nội dung file import bị trùng.";
                        lblMsgDuplicateFile.Visible = true;
                        checkduplicate = true;
                        break;
                    }
                    else
                    {
                        lblMsgDuplicateFile.Text = "";
                        lblMsgDuplicateFile.Visible = false;
                    }
                }
            }
            if (checkduplicate)
            {
                MessageBoxFileDuplicate.ShowOnPageLoad = true;
                return;
            }

            ImportFileExcel();
        }
        else
        {
            lblMsg.Text = "Chưa tải tập tin lên hệ thống. Bạn phải chọn tập tin Excel cần import.";
            lblMsg.Visible = true;
            gviewList.DataSource = null;
            gviewList.DataBind();
        }
                
    }
    private bool CheckSheetName(OleDbConnection objConn, string SheetNameCheck)
    {
        bool flag = false;
        System.Data.DataTable dt = null;
        SheetNameCheck = SheetNameCheck + "$";

        try
        {
            objConn.Open();
            // Get the data table containg the schema guid.
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                return flag;
            }

            foreach (DataRow row in dt.Rows)
            {
                string SheetName = row["TABLE_NAME"].ToString();
                if (SheetName == SheetNameCheck)
                {
                    flag = true;
                    break;
                }

            }
        }
        catch (Exception ex)
        {
            return flag;
        }
        finally
        {
            // Clean up.
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }            
        }
        return flag;
    }
    protected bool Excel2Sql(string PhysicalPathFolder, string fileName)
    {
        //string strSource = Globals.PhysicalApplicationPath() + "/UserUpload/BaoCaoExcel/" + fileName;
        bool flag = false;
        string strSource = Globals.PhysicalApplicationPath() + PhysicalPathFolder + fileName;
        //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSource + ";Extended Properties=Excel 12.0;Persist Security Info=False";

        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSource + ";Extended Properties=Excel 12.0;Persist Security Info=False;");

        if (CheckSheetName(oconn, "DKSH"))
        {
            try
            {
                oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSource + ";Extended Properties=Excel 12.0;Persist Security Info=False;");

                dt = new ClassExecute.ClassExecute();
                dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "TTr_TempImportDKSH_Delete", ref error, 
                    new SqlParameter("@UserName", Session["user"].ToString()));
                ////string strSiteCode = "FM_00000003"; //Session["sitecode"].ToString();

                string sql = "select KHO,MSVT,TENVT,MKH,TENKH,TENKH1,DC,QHT,MAQUAN,CTYPE,CHANNEL,LAND,DISTRICT,INVOICE,NGAY,TDV,QTY";
                sql = sql + ",DGLE,TT,TTV,NGUYENGIA,'" + strSiteCodeDistributor + "' as SiteCode,'" + fileName + "' as FileName from [DKSH$] ";

                OleDbCommand ocmd = new OleDbCommand(sql, oconn);
                oconn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(ocmd);
                DataTable dbTest = new DataTable();
                da.Fill(dbTest);
                oconn.Close();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strconn))
                {
                    bulkCopy.BatchSize = 500;
                    bulkCopy.NotifyAfter = 1000;
                    //bulkCopy.SqlRowsCopied +=
                    //    new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                    bulkCopy.DestinationTableName = "dbo.TempImportDKSH" + Session["user"].ToString(); 
                    bulkCopy.WriteToServer(dbTest);

                }
                flag = true;

            }
            catch (DataException ex)
            {
                txtFileName.Value = "";
                lblFileName.Text = "";
                lblMsg.Text = "Tập tin " + fileName + " không đúng định dạng của NPP";
                lblMsg.Visible = true;
                string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
                dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
                
            }
            finally
            {
                // Clean up.
                if (oconn != null)
                {
                    oconn.Close();
                    oconn.Dispose();
                }
            }
        }
        else
        {
            lblMsg.Text = "Không đúng tên sheet.Tên sheet phải là [DKSH]";
            lblMsg.Visible = true;
        }
        return flag;
    }
    private static void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
    {
        Console.WriteLine("Copied {0} so far...", e.RowsCopied);
    }
    protected void btnView_Click(object sender, EventArgs e)
    {
        if (txtFileName.Value != "")
        {
            try
            {
                if (Excel2Sql(@"/UserUpload/UploadTempFileExcel/", txtFileName.Value))
                {
                    dt = new ClassExecute.ClassExecute();
                    DataBind();
                }
            }
            catch (Exception ex)
            {
                txtFileName.Value = "";
                lblFileName.Text = "";
                lblMsg.Text = "Tập tin " + txtFileName.Value + " không đúng định dạng của NPP";
                lblMsg.Visible = true;
                string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
                dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
                return;
            }
        }
        else
        {
            lblMsg.Text = "Chưa tải tập tin lên hệ thống.";
            lblMsg.Visible = true;
        }        
        
    }
    protected void gvCustomerNewBuyProduct_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvCustomerNewBuyProduct.PageIndex = e.NewPageIndex;
        dt = new ClassExecute.ClassExecute();
        DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TTr_NewCustomBuyProduct_GetAll", CommandType.StoredProcedure
                    , new SqlParameter("@CustID", txtNewCustIDBuyPro.Value)
                    , new SqlParameter("@Sitecode", txtStieCodeNewCustBuyPro.Value));

        if (db.Rows.Count > 0)
        {
            gvCustomerNewBuyProduct.DataSource = db;
            gvCustomerNewBuyProduct.DataBind();
        }
        else
        {
            gvCustomerNewBuyProduct.DataSource = null;
            gvCustomerNewBuyProduct.DataBind();
        }
    }
    protected void gvrNewCustList_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvrNewCustList.PageIndex = e.NewPageIndex;
        dt = new ClassExecute.ClassExecute();
        DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempNewCustDKSH_GetAll", CommandType.StoredProcedure);
        if (db.Rows.Count > 0)
        {
            gvrNewCustList.DataSource = db;
            gvrNewCustList.DataBind();
        }
    }
    protected void gvrNewProductList_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvrNewProductList.PageIndex = e.NewPageIndex;
        dt = new ClassExecute.ClassExecute();
        DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TempProductDKSH_GetAll", CommandType.StoredProcedure);
        if (db.Rows.Count > 0)
        {
            gvrNewProductList.DataSource = db;
            gvrNewProductList.DataBind();
        }
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        if (txtFileName.Value != "")
        {
            int result = FileProcess.DeleteFile(Globals.PhysicalApplicationPath() + "/UserUpload/UploadTempFileExcel/" + txtFileName.Value);
            if (result != 1)
            {
                string YyyyMm = DateTime.Now.ToString("yyyyMM");
                result = FileProcess.DeleteFile(Globals.PhysicalApplicationPath() + "/UserUpload/ImportedFileExcel/" + YyyyMm + "/" + txtfileNameDestination.Value);
            }

            if (result == 1)
            {
                lblMsg.Text = "Xóa tập tin [" + txtFileName.Value + "] thành công.";

                txtFileName.Value = "";
                lblFileName.Text = "";
                txtfileNameDestination.Value = "";
                txtfileNameDestinationInsert.Value = "";
                gviewList.DataSource = null;
                gviewList.DataBind();
                DisplayPager.Visible = false;
                lblMsg.Visible = true;
            }
            else
            {
                lblMsg.Text = "Tập tin " + txtFileName.Value + " không tồn tại.";
                lblMsg.Visible = true;
            }
        }
        else
        {
            lblMsg.Text = "Chưa tải tập tin lên hệ thống.";
            lblMsg.Visible = true;
        }
    }
    protected void gvrNewProductList_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        try
        {
            if (e.CommandName == "chon")
            {
                GridViewRow row = ((Control)e.CommandSource).NamingContainer as GridViewRow;
                DropDownList drlNhomSanPham = row.FindControl("drlNhomSanPham") as DropDownList;
                DropDownList drlKenhSanPham = row.FindControl("drlKenhSanPham") as DropDownList;
                //TextBox txtQuyCach = row.FindControl("txtQuyCach") as TextBox;
                Label lblMaHang = row.FindControl("lblMaHang") as Label;
                Label lblSitecode = row.FindControl("lblSitecode") as Label;

                /////insert vao product new
                int result=0;
                 result = dt.ExecuteSQl_int(strconn, "TTr_ProductNew_ActiveNew", CommandType.StoredProcedure, ref error,
                    new SqlParameter("@Sitecode", lblSitecode.Text),
                    new SqlParameter("@itemkey", lblMaHang.Text),
                    new SqlParameter("@commodkey", drlNhomSanPham.SelectedValue.ToString()),
                    new SqlParameter("@inclasskey", drlKenhSanPham.SelectedValue.ToString()));
                 if (result == 1)
                 {
                     lblMsgResultNewProduct.Text = "Thêm sản phẩm mới thành công";
                     lblMsgResultNewProduct.Visible = true;
                     DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TempProductDKSH_GetAll", CommandType.StoredProcedure);
                     if (db.Rows.Count > 0)
                     {
                         gvrNewProductList.DataSource = db;
                         gvrNewProductList.DataBind();
                     }
                     else
                     {
                         gvrNewProductList.DataSource = null;
                         gvrNewProductList.DataBind();
                     }
                 }
                 else
                 {
                     lblMsgResultNewProduct.Text = "Thêm sản phẩm mới thất bại";
                     lblMsgResultNewProduct.Visible = true;
                 }
                    

            }
        }
        catch (Exception ex)
        {
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
            return;
        }
    }
    protected void gvrNewCustList_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        try
        {
            if (e.CommandName == "chon")
            {
                GridViewRow row = ((Control)e.CommandSource).NamingContainer as GridViewRow;
                Label lblMaKhachHang = row.FindControl("lblMaKhachHang") as Label;
                Label lblSitecode = row.FindControl("lblSitecode") as Label;

                /////insert vao product new
                int result = 0;
                result = dt.ExecuteSQl_int(strconn, "TTr_CustomerNew_ActiveNew", CommandType.StoredProcedure, ref error,
                   new SqlParameter("@Sitecode", lblSitecode.Text),
                   new SqlParameter("@CustID", lblMaKhachHang.Text));
                if (result == 1)
                {
                    lblMsgResultNewCust.Text = "Xác nhận khách hàng mới thành công";
                    lblMsgResultNewCust.Visible = true;
                    DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempNewCustDKSH_GetAll", CommandType.StoredProcedure);
                    if (db.Rows.Count > 0)
                    {
                        gvrNewCustList.DataSource = db;
                        gvrNewCustList.DataBind();
                    }
                    else
                    {
                        gvrNewCustList.DataSource = null;
                        gvrNewCustList.DataBind();
                    }
                }
                else
                {
                    lblMsgResultNewCust.Text = "Xác nhận khách hàng mới thất bại";
                    lblMsgResultNewCust.Visible = true;
                }
            }
            if (e.CommandName == "ViewBuyProduct")
            {
                GridViewRow row = ((Control)e.CommandSource).NamingContainer as GridViewRow;
                Label lblMaKhachHang = row.FindControl("lblMaKhachHang") as Label;
                Label lblSitecode = row.FindControl("lblSitecode") as Label;
                Label lblCustomerNameNew = row.FindControl("lblCustomerNameNew") as Label;
                txtNewCustIDBuyPro.Value = lblMaKhachHang.Text;
                txtStieCodeNewCustBuyPro.Value = lblSitecode.Text;
                DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TTr_NewCustomBuyProduct_GetAll", CommandType.StoredProcedure
                    , new SqlParameter("@CustID", lblMaKhachHang.Text)
                    , new SqlParameter("@Sitecode", lblSitecode.Text));

                if (db.Rows.Count > 0)
                {
                    gvCustomerNewBuyProduct.DataSource = db;
                    gvCustomerNewBuyProduct.DataBind();
                    MsgBoxCustNewBuyProductDetail.Text = "Những sản phẩm đã mua của khách hàng: " + lblMaKhachHang.Text + "_" + lblCustomerNameNew.Text;
                    MsgBoxCustNewBuyProductDetail.ShowOnPageLoad = true;
                }
                else
                {
                    gvCustomerNewBuyProduct.DataSource = null;
                    gvCustomerNewBuyProduct.DataBind();
                }
            }

        }
        catch (Exception ex)
        {
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
            return;
        }
    }
    protected void gvrNewProductList_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            dt = new ClassExecute.ClassExecute();

            // Display the Group.
            DropDownList drlNhomSanPham = (DropDownList)e.Row.Controls[0].FindControl("drlNhomSanPham");
            DataTable db = dt.Get_Data_DataTable(strconn, ref error, "[ls_getCategoryList]", CommandType.StoredProcedure);
            dt.BindDropDownList(db, drlNhomSanPham, "CommodKey", "Description");
            

            // Display the Channel.
            DropDownList drlKenhSanPham = (DropDownList)e.Row.Controls[0].FindControl("drlKenhSanPham");
            DataTable db1 = dt.Get_Data_DataTable(strconn, ref error, "[k_getInclassFilter]", CommandType.StoredProcedure,
               new SqlParameter("@CatID", drlNhomSanPham.SelectedValue.ToString()));
            dt.BindDropDownList(db1, drlKenhSanPham, "MaNhanHieu", "NhanHieu");
        }
    }
    protected void btnActiveCustNewAll_Click(object sender, EventArgs e)
    {
        int result = 0;
        dt = new ClassExecute.ClassExecute();
        result = dt.ExecuteSQl_int(strconn, "TTr_CustomerNew_ActiveNewAll", CommandType.StoredProcedure, ref error);

        if (result == 1)
        {
            lblMsgResultNewCust.Text = "Xác nhận tất cả khách hàng mới thành công";
            lblMsgResultNewCust.Visible = true;
            gvrNewCustList.DataSource = null;
            gvrNewCustList.DataBind();
            //lblMessageBox.Text = "Cập nhật tất cả khách mới thành công";
        }
        else
        {
            lblMsgResultNewCust.Text = "Xác nhận tất cả khách hàng mới thất bại";
            lblMsgResultNewCust.Visible = true;
        }
    }
    protected void lbtShowCustNew_Click(object sender, EventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        DataTable dtNewCust = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempNewCustDKSH_GetAll", CommandType.StoredProcedure);
        if (dtNewCust.Rows.Count > 0)
        {
            gvrNewCustList.DataSource = dtNewCust;
            gvrNewCustList.DataBind();
            btnActiveCustNewAll.Visible = true;
            ShowMessageBox("Danh sách khách hàng mới đã được thêm vào hệ thống. Bạn hãy xác nhận những khách hàng này để được tính doanh số.");
        }
    }
    protected void lbtShowProductNew_Click(object sender, EventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        DataTable dbNewProduct = dt.Get_Data_DataTable(strconn, ref error, "TempProductDKSH_GetAll", CommandType.StoredProcedure);
        if (dbNewProduct.Rows.Count > 0)
        {
            gvrNewProductList.DataSource = dbNewProduct;
            gvrNewProductList.DataBind();
            ShowMessageBoxNewProduct("Danh sách sản phẩm mới đã được thêm vào hệ thống. Bạn hãy xác nhận, cập nhật thông tin cho những sản phẩm này.");
        }    
    }
    private void VisibleLinkbtnMsg()
    {
        dt = new ClassExecute.ClassExecute();
        if (dt.ExecuteSQl_int(strconn, "TTr_CheckExistsNewCust", CommandType.StoredProcedure, ref error) == 1)
        {
            lbtShowCustNew.Visible = true;
        }
        else
        {
            lbtShowCustNew.Visible = false;
        }

        if (dt.ExecuteSQl_int(strconn, "TTr_CheckExistsNewProduct", CommandType.StoredProcedure, ref error) == 1)
        {
            lbtShowProductNew.Visible = true;
        }
        else
        {
            lbtShowProductNew.Visible = false;
        }
    }
}
